with interceptor_area as ( --区域异常
    select province
         , province_id
         , city
         , city_id
         , area
         , area_id
         , if(effect_time_start is null, '1999-01-01 01:01:01', effect_time_start) as effect_time_start
         , if(effect_time_end is null, '9999-01-01 01:01:01', effect_time_end)     as effect_time_end
    from jms_dim.dim_yl_oms_order_interceptor_base
    where data_type = 'area'
      and is_delete = 0           --未删除
      and is_enable = 1           --已启用 0311001
      and ((effect_time_start is not null and effect_time_end is not null)
        or (effect_time_start is  null and effect_time_end is  null and
            effect_time_type = 0))--生效时间类型 0 无限 ，1 有效时间断
    group by province
           , province_id
           , city
           , city_id
           , area
           , area_id
           , effect_time_start
           , effect_time_end
),
     area_massage_all as (
         select t2.code
              , t2.name
              , t1.effect_time_start
              , t1.effect_time_end
         from (--区域异常,市为空
                  select *
                  from interceptor_area
                  where province is not null
                    and city is null
              ) t1 --区域异常,市为空
                  left join jms_dim.dim_network_whole_massage t2
                            on t1.province = t2.provider_desc
         union all
         select t2.code
              , t2.name
              , if(effect_time_start is null, '1999-01-01 01:01:01', effect_time_start) as effect_time_start
              , if(effect_time_end is null, '9999-01-01 01:01:01', effect_time_end)     as effect_time_end
         from (--区域异常,区域为空
                  select *
                  from interceptor_area
                  where province is not null
                    and city is not null
                    and area is null
              ) t1 --区域异常,区域为空
                  left join jms_dim.dim_network_whole_massage t2
                            on t1.province = t2.provider_desc
                                and city = t2.city_desc
         union all
         select t2.code
              , t2.name
              , t1.effect_time_start
              , t1.effect_time_end
         from (--区域异常,省市区都不为空
                  select *
                  from interceptor_area
                  where province is not null
                    and city is not null
                    and area is not null
              ) t1 --区域异常,省市区都不为空
                  left join jms_dim.dim_network_whole_massage t2
                            on t1.province = t2.provider_desc
                                and city = t2.city_desc
     ),
     interceptor_network as ( --网点异常
         select network_code
              , network_name
              , if(effect_time_start is null, '1999-01-01 01:01:01', effect_time_start) as effect_time_start
              , if(effect_time_end is null, '9999-01-01 01:01:01', effect_time_end)     as effect_time_end
         from jms_dim.dim_yl_oms_order_interceptor_base
         where data_type = 'network'
           and is_delete = 0           --未删除
           and is_enable = 1           --已启用
           and network_code is not null
           and ((effect_time_start is not null and effect_time_end is not null)
             or (effect_time_start is  null and effect_time_end is  null and
                 effect_time_type = 0))--生效时间类型 0 无限 ，1 有效时间断
         group by network_code
                , network_name
                , effect_time_start
                , effect_time_end
     ),

     interceptor_third as ( --三段码异常
         select network_code
              , network_name
              , the_third_code
              , effect_time_start
              , effect_time_end
         from jms_dim.dim_yl_oms_order_interceptor_base
         where data_type = 'the_third_segment_code'
           and is_delete = 0           --未删除
           and is_enable = 1           --已启用
           and (the_third_code is not null and network_code is not null)
           and ((effect_time_start is not null and effect_time_end is not null)
             or (effect_time_start is  null and effect_time_end is  null and
                 effect_time_type = 0))--生效时间类型 0 无限 ，1 有效时间断
         group by network_code
                , network_name
                , the_third_code
                , effect_time_start
                , effect_time_end
     )
insert
overwrite
table
jms_dm.dm_customer_complaint_order_interceptor_dt
partition
(
dt
)
select network_code
     , network_name
     , the_third_code
     , effect_time_start
     , effect_time_end
     , type --区域类型
     , dt
from (
         select network_code
              , network_name
              , null         as the_third_code
              , effect_time_start
              , effect_time_end
              , 1            as type --区域类型
              , '{{ execution_date | cst_ds }}' as dt
         from (
                  select code              as network_code
                       , name              as network_name
                       , effect_time_start as effect_time_start
                       , effect_time_end   as effect_time_end
                  from area_massage_all
                  union all
                  select network_code
                       , network_name
                       , effect_time_start as effect_time_start
                       , effect_time_end   as effect_time_end
                  from interceptor_network
              ) a
         group by network_code
                , network_name
                , effect_time_start
                , effect_time_end
                , dt

         union all
         select network_code
              , network_name
              , the_third_code
              , effect_time_start as effect_time_start
              , effect_time_end   as effect_time_end
              , 3                 as type --三段码异常
              , '{{ execution_date | cst_ds }}'      as dt
         from interceptor_third
     ) a
where network_code is not null
  and network_code <> ''
    distribute by 1
;
